﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using PPAP.Model;
using System.Data.OracleClient;

namespace PPAP.DAO
{
    public class MailTemplateDAO
    {
        public MailTemplateDAO() { }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(MailTemplate model)
        {
            if (model.ISUSE == 1)  //如果此模板为使用状态，将其他模板置为未使用状态
                UpdateUse();
            model.ID = DbHelperOra.GetMaxID("ID", "MAILTEMPLATE");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into MAILTEMPLATE(");
            strSql.Append("ID,NAME,CONTENT,TITLE,ISUSE,CREATETIME,CREATEDBY,OPPORTUNITY,RECIPIENTS)");
            strSql.Append(" values (");
            strSql.Append(":ID,:NAME,:CONTENT,:TITLE,:ISUSE,:CREATETIME,:CREATEDBY,:OPPORTUNITY,:RECIPIENTS)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":NAME", OracleType.VarChar,350),
					new OracleParameter(":CONTENT", OracleType.VarChar,4000),
					new OracleParameter(":TITLE", OracleType.VarChar,350),
					new OracleParameter(":ISUSE", OracleType.Number,4),
                                           new OracleParameter(":CREATETIME",OracleType.DateTime),
                                           new OracleParameter(":CREATEDBY",OracleType.VarChar,50),
                                           new OracleParameter(":OPPORTUNITY",OracleType.VarChar,350),
                                           new OracleParameter(":RECIPIENTS",OracleType.VarChar,350)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.NAME;
            parameters[2].Value = model.CONTENT;
            parameters[3].Value = model.TITLE;
            parameters[4].Value = model.ISUSE;
            parameters[5].Value = model.CREATETIME;
            parameters[6].Value = model.CREATEDBY;
            parameters[7].Value = model.Opportunity;
            parameters[8].Value = model.Recipients;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters)>0?int.Parse(model.ID.ToString()):0;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(MailTemplate model)
        {
            //if (model.ISUSE == 1)  //如果此模板为使用状态，将其他模板置为未使用状态
            //    UpdateUse();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update MAILTEMPLATE set ");
            strSql.Append("ID=:ID,");
            strSql.Append("NAME=:NAME,");
            strSql.Append("CONTENT=:CONTENT,");
            strSql.Append("TITLE=:TITLE,");
            strSql.Append("ISUSE=:ISUSE,CREATETIME=:CREATETIME,CREATEDBY=:CREATEDBY,OPPORTUNITY=:OPPORTUNITY,RECIPIENTS=:RECIPIENTS");
            strSql.Append(" where id="+model.ID);
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":NAME", OracleType.VarChar,350),
					new OracleParameter(":CONTENT", OracleType.VarChar,4000),
					new OracleParameter(":TITLE", OracleType.VarChar,350),
					new OracleParameter(":ISUSE", OracleType.Number,4),
                                           new OracleParameter(":CREATETIME",OracleType.DateTime),
                                           new OracleParameter(":CREATEDBY",OracleType.VarChar,50),
                                           new OracleParameter(":OPPORTUNITY",OracleType.VarChar,350),
                                           new OracleParameter(":RECIPIENTS",OracleType.VarChar,350)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.NAME;
            parameters[2].Value = model.CONTENT;
            parameters[3].Value = model.TITLE;
            parameters[4].Value = model.ISUSE;
            parameters[5].Value = model.CREATETIME;
            parameters[6].Value = model.CREATEDBY;
            parameters[7].Value = model.Opportunity;
            parameters[8].Value = model.Recipients;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 将所有模板置为未使用状态
        /// </summary>
        public void UpdateUse()
        {
            string sql = "update MAILTEMPLATE set isuse=0";
            DbHelperOra.ExecuteSql(sql);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from MAILTEMPLATE ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public MailTemplate GetModel(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,NAME,CONTENT,TITLE,ISUSE,CREATETIME,CREATEDBY,OPPORTUNITY,RECIPIENTS from MAILTEMPLATE ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

            MailTemplate model = new MailTemplate();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.NAME = ds.Tables[0].Rows[0]["NAME"].ToString();
                model.CONTENT = ds.Tables[0].Rows[0]["CONTENT"].ToString();
                model.TITLE = ds.Tables[0].Rows[0]["TITLE"].ToString();
                if (ds.Tables[0].Rows[0]["ISUSE"].ToString() != "")
                {
                    model.ISUSE = int.Parse(ds.Tables[0].Rows[0]["ISUSE"].ToString());
                }
                model.CREATEDBY = ds.Tables[0].Rows[0]["createdby"].ToString();
                model.CREATETIME = DateTime.Parse(ds.Tables[0].Rows[0]["createtime"].ToString());
                model.Opportunity = ds.Tables[0].Rows[0]["OPPORTUNITY"].ToString();
                model.Recipients = ds.Tables[0].Rows[0]["RECIPIENTS"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public MailTemplate GetModelForUse()
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,NAME,CONTENT,TITLE,ISUSE,CREATETIME,CREATEDBY,OPPORTUNITY,RECIPIENTS from MAILTEMPLATE ");
            strSql.Append(" where ISUSE=1");
            OracleParameter[] parameters = {
};

            MailTemplate model = new MailTemplate();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.NAME = ds.Tables[0].Rows[0]["NAME"].ToString();
                model.CONTENT = ds.Tables[0].Rows[0]["CONTENT"].ToString();
                model.TITLE = ds.Tables[0].Rows[0]["TITLE"].ToString();
                if (ds.Tables[0].Rows[0]["ISUSE"].ToString() != "")
                {
                    model.ISUSE = int.Parse(ds.Tables[0].Rows[0]["ISUSE"].ToString());
                }
                model.CREATEDBY = ds.Tables[0].Rows[0]["createdby"].ToString();
                model.CREATETIME = DateTime.Parse(ds.Tables[0].Rows[0]["createtime"].ToString());
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.*,rownum from ( select ID,NAME,CONTENT,TITLE,ISUSE,CREATETIME,CREATEDBY,OPPORTUNITY,RECIPIENTS ");
            strSql.Append(" FROM MAILTEMPLATE order by id)t  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOra.Query(strSql.ToString());
        }

        /// <summary>
        /// 根据pparID获取partno,partname,qe-email  edm2
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public DataSet GetEDM2(string pparid)
        {
            string sql = @"select r.bdntpartno,r.bdntpartname,u.email,r.color from ppar a left join part r on a.partid=r.id
                            left join users u on u.username=a.qeuser where a.id=" +pparid;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据pparid获取partno,partname,supplier-email  edm3,edm4,edm5,edm12,edm13  发给供应商管理员
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public DataSet GetEDM3(string pparid)
        {
            string sql = @"select r.supplieradmin as username,r.bdntpartname,r.bdntpartno from ppar a left join part r on a.partid=r.id  where a.id=" + pparid;
            sql = string.Format(@"select t.* from (
                select r.supplieradmin as username,r.bdntpartname,r.bdntpartno,a.submittedby,a.finalresult,r.color from ppar a left join part r on a.partid=r.id  where a.id={0}
                union 
                select c.email as username,r.bdntpartname,r.bdntpartno,a.submittedby,a.finalresult,r.color from ppar a left join part r on a.partid=r.id
                left join plant p on r.supplieradmin=p.supplieradmin
                left join contactperson c on p.id=c.plantid  where a.id={0} and c.ismanager=1) t group by t.username,t.bdntpartname,t.bdntpartno,t.submittedby,t.finalresult,t.color", pparid);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据samplecardid获取partno,partname,te-email  dem6
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public DataSet GetEDM6(string samplecardid)
        {
            string sql = @"select u.email,r.bdntpartname,r.bdntpartno,r.color from samplecard s left join users u on s.deptrep=u.username
                        left join ppar a on s.pparid=a.id
                        left join part r on a.partid=r.id
                        where s.id=" + samplecardid;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据samplecardid获取partno,partname,te的上级tm-email  dem8 中国TM
        /// </summary>
        /// <param name="samplecardid"></param>
        /// <returns></returns>
        public DataSet GetEDM8(string samplecardid)
        {
            string sql = @"select e.email,r.bdntpartname,r.bdntpartno,t.testitemname,r.color from samplecard s 
                        left join testdepartment t on s.testdepartmentid=t.id
                        left join users u on s.deptrep=u.username
                        left join ppar a on s.pparid=a.id
                        left join part r on a.partid=r.id
                        left join users e on u.superior1=e.username
                        where s.id=" + samplecardid;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据samplecardid获取partno,partname,te的上级tm-email  dem8  德国TM
        /// </summary>
        /// <param name="samplecardid"></param>
        /// <returns></returns>
        public DataSet GetEDM82(string samplecardid)
        {
            string sql = @"select e.email,r.bdntpartname,r.bdntpartno,t.testitemname,r.color from samplecard s 
                        left join testdepartment t on s.testdepartmentid=t.id
                        left join users u on s.deptrep=u.username
                        left join ppar a on s.pparid=a.id
                        left join part r on a.partid=r.id
                        left join users e on u.superior2=e.username
                        where s.id=" + samplecardid;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据samplecardid获取partno,partname,qe-email  dem9
        /// </summary>
        /// <param name="samplecardid"></param>
        /// <returns></returns>
        public DataSet GetEDM9(string samplecardid)
        {
            string sql = string.Format(@"select u.email,r.bdntpartno,r.bdntpartname,r.color from ppar a
                        left join part r on a.partid=r.id
                        left join users u on a.qeuser=u.username
                        where a.id=(
                        select pparid from samplecard where id={0})", samplecardid);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据pparid获取partno,partname,qe上级qm-email dem10
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public DataSet GetEDM10(string pparid)
        {
            string sql = string.Format(@"select s.email,r.bdntpartno,r.bdntpartname,r.color from ppar a
                        left join part r on a.partid=r.id
                        left join users u on a.qeuser=u.username
                        left join users s on u.superior=s.username
                        where a.id={0}", pparid);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据pparid获取partno,partname,qe-email
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public DataSet GetEDM14(string pparid)
        {
            string sql = string.Format(@" select u.email,r.bdntpartno,r.bdntpartname,r.color from ppar a
                        left join part r on a.partid=r.id
                        left join users u on a.qeuser=u.username
                        where a.id={0}",pparid);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据pparid获取partno,partname
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public DataSet GetEDM11(string pparid)
        {
            string sql = string.Format(" select r.bdntpartno,r.bdntpartname,r.color from ppar a left join part r on a.partid=r.id where a.id={0}", pparid);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据pparID获取partno,partname,supplieradmin-email  edm17
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public DataSet GetEDM17(string pparid)
        {
            string sql = @"select r.bdntpartno,r.bdntpartname,r.supplieradmin,r.color from ppar a left join part r on a.partid=r.id where a.id=" + pparid;
            return DbHelperOra.Query(sql);
        }
    }
}
